USE [LAFDC]
IF EXISTS (select 1 from sys.objects where type = 'P' and name = 'Carte_Adherent_AdEtSection')
BEGIN
	DROP PROCEDURE [dbo].[Carte_Adherent_AdEtSection]
END
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Carte_Adherent_AdEtSection]
	-- Add the parameters for the stored procedure here
	@asso as varchar(3),
	@section as varchar(255) 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

		SELECT
					upper(p.nom) as 'NOM_ADH',
					case (len(prenom)) when 0 then '' else upper(substring(p.prenom,1,1)) + lower(substring(p.prenom, 2, len(p.prenom)-1)) end as 'PRENOM_ADH',
					adr.adresse_1 + case adr.adresse_2 when '' then '' else ', ' + adr.adresse_2 end as 'ADR_ADH',
					adr.codepostal as 'CPOSTAL_ADH',
					adr.ville as 'VILLE_ADH',
					a.prev_id as 'CP_ADH',
					p.sfam as 'SITUATION_ADH',
					(select count (*) from personne where personne.prev_id = a.prev_id and personne.sfam = 'Enfant') as 'NB_ENFANT',
					dbo.anniv_liste(a.prev_id) as 'LISTE_DOB',
					isnull((select top 1 valeur from contact where prev_id = a.prev_id and type = 'Telephone'), '') as 'TEL_ADH',
					isnull((select top 1 valeur from contact where prev_id = a.prev_id and type = 'email'), '') as 'MAIL_ADH',
					isnull((select top 1 case affectation when '0' then '' else UPPER(affectation) end from divers where prev_id = a.prev_id), '') as 'ADR_SNCF',
					'' as 'CPOSTAL_SNCF',
					'' as 'VILLE_SNCF',
					(select top 1 valeur from contact where prev_id = a.prev_id and type = 'SNCF') as 'TEL_SNCF'
		  from adherent a
		  join personne p
			on a.prev_id = p.prev_id and sfam != 'enfant' and sfam != 'conjoint(e)'
		  join adresse adr
			on a.prev_id = adr.prev_id
		 where a.ad = @asso
		   and a.section = @section
/* TEST RUN:
exec Carte_Adherent_AdEtSection 75, '0'
 */
END

